Power Programmierung/Power-Programmierung (Tewi)(1994).iso/magazine/dbms_mag/9108/roti2.aug

Text File | 1991-06-27 | 2KB | 70 lines

Listing 2

DECLARE
  CURSOR trans IS
  SELECT transaction_number, account_id,
         transaction_type, amount
  FROM   transactions
  ORDER  BY transaction_number
  FOR    UPDATE OF posted;
  used   NUMBER(6);
  limit  NUMBER(6);
  purchase_too_big EXCEPTION;
  payment_too_big EXCEPTION;
  invalid_transaction_type EXCEPTION;
BEGIN
  FOR t IN trans LOOP
    BEGIN -- start an inner block
      -- get credit used/limit and validate account
      SELECT credit_used, credit_limit
      INTO   used, limit
      FROM   accounts
      WHERE  account_id = t.account_id;
      -- process transaction based on type:
      -- P = purchase, C = credit (payment)
      IF t.transaction_type = 'P' THEN
        IF t.amount > limit - used THEN
          RAISE purchase_too_big;
        END IF;
        UPDATE accounts
        SET    credit_used = credit_used + t.amount
        WHERE  account_id = t.account_id;
      ELSIF t.transaction_type = 'C' THEN
        IF t.amount > used THEN
          RAISE payment_too_big;
        END IF;
        UPDATE accounts
        SET    credit_used = credit_used - t.amount
        WHERE  account_id = t.account_id;
      ELSE
        RAISE invalid_transaction_type;
      END IF;
      -- mark transaction as posted if update succeeded
      UPDATE transactions
      SET    posted = 'Y'
      WHERE  CURRENT OF trans;
    -- handle error conditions
    EXCEPTION
      WHEN NO_DATA_FOUND THEN -- account_id not found
        INSERT INTO errors
        VALUES (t.transaction_number,
                'Invalid account: ' || t.account_id);
      WHEN purchase_too_big THEN
        INSERT INTO errors
        VALUES (t.transaction_number,
                'Purchase too big: ' || TO_CHAR(t.amount));
      WHEN payment_too_big THEN
        INSERT INTO errors
        VALUES (t.transaction_number,
                'Payment too big: ' || TO_CHAR(t.amount));
      WHEN invalid_transaction_type THEN
        INSERT INTO errors
        VALUES (t.transaction_number,
                'Invalid type: ' || t.transaction_type);
    END;
  END LOOP;
  COMMIT;
END;
/